----------------------
--SALTO Script--
----------------------

DROP TABLE [dbo].[CardHolderSyncTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[CardHolderSyncTable](
	[ExtUserID] [nvarchar](32) NOT NULL,
	[LastUpdateDate] [datetime] NULL,
	[Action] [int] NULL,
	[FirstName] [nvarchar](40) NULL,
	[LastName] [nvarchar](40) NULL,
	[AuditOpenings] [bit] NULL,
	[ExtAccessLevelIDList] [nvarchar](1024) NULL,
	[AutoKeyEdit.ROMCode] [nvarchar](32) NULL,
	[ToBeProcessedBySalto] [int] NULL,   
	[ProcessedDateTime] [datetime] NULL, 
	[ErrorCode] [int] NOT NULL Default 0,
	[ErrorMessage] [nvarchar](512) NULL,                            
 CONSTRAINT [PK_CardHolderSyncTable] PRIMARY KEY CLUSTERED 
(
	[ExtUserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DROP TRIGGER [dbo].[CRDHLDSyncTrigDel]
GO

CREATE TRIGGER [dbo].[CRDHLDSyncTrigDel] ON [dbo].[CRDHLD] 
AFTER DELETE 
AS 
BEGIN 

	SET NOCOUNT ON; 


	insert into CardHolderSyncTable 
	([ExtUserID], [LastUpdateDate], [Action],[FirstName], [LastName], [AuditOpenings], [ExtAccessLevelIDList], [AutoKeyEdit.ROMCode], [ToBeProcessedBySalto])  
	(select id,     GetDate(),     4,      First_Name, Last_Name,     1,              case len(isnull(ag_name,''))  when 0 then ''
   else left(rtrim(ag_name), len(rtrim(ag_name))-1)  
   END,            NULL ,        1
	from  deleted
	where
	deleted.id not in (select ExtUserID from CardHolderSyncTable))

	update CardHolderSyncTable set
	[LastUpdateDate] = GetDate(), [Action] = 4,[FirstName] = First_Name, [LastName] = Last_Name, [AuditOpenings] = 1, [ExtAccessLevelIDList] = case len(isnull(ag_name,''))  when 0 then ''
   else left(rtrim(ag_name), len(rtrim(ag_name))-1)  
   END, [ToBeProcessedBySalto] = 1
	from  DELETED
	where 
	DELETED.id in (select ExtUserID from CardHolderSyncTable)
	AND CardHolderSyncTable.ExtUserID = DELETED.id
	
END

GO

DROP TRIGGER [dbo].[CRDHLDSyncTrigUpdate]
GO

CREATE TRIGGER [dbo].[CRDHLDSyncTrigUpdate] ON [dbo].[CRDHLD] 
AFTER UPDATE,INSERT 
AS 
BEGIN 

	SET NOCOUNT ON; 


	insert into CardHolderSyncTable 
	([ExtUserID], [LastUpdateDate], [Action],[FirstName], [LastName], [AuditOpenings], [ExtAccessLevelIDList], [AutoKeyEdit.ROMCode], [ToBeProcessedBySalto])  
	(select id,     GetDate(),     
	case when INSERTED.type = 3  then 4 
		 when INSERTED.type <> 3 then 3
	end,      
	First_Name, Last_Name,     1,               case len(isnull(ag_name,''))  when 0 then ''
   else left(rtrim(ag_name), len(rtrim(ag_name))-1)  
   END ,            NULL , 1
	from  INSERTED
	where 
	INSERTED.id not in (select ExtUserID from CardHolderSyncTable))

	update CardHolderSyncTable set
	[LastUpdateDate] = GetDate(), 
	[Action] = case when INSERTED.type = 3  then 4 
			        when INSERTED.type <> 3 then 3
				end
	,[FirstName] = First_Name, [LastName] = Last_Name, [AuditOpenings] = 1, [ExtAccessLevelIDList] =  case len(isnull(ag_name,''))  when 0 then ''
   else left(rtrim(ag_name), len(rtrim(ag_name))-1)  
   END ,  [ToBeProcessedBySalto] = 1
	from  INSERTED
	where INSERTED.id in (select ExtUserID from CardHolderSyncTable)
	AND CardHolderSyncTable.ExtUserID = INSERTED.id
	
END


GO

DROP TRIGGER [dbo].[CardSyncTrigDel]
GO

CREATE TRIGGER [dbo].[CardSyncTrigDel] ON [dbo].[Card] 
AFTER DELETE 
AS 
BEGIN 

	SET NOCOUNT ON; 


	insert into CardHolderSyncTable 
	([ExtUserID], [LastUpdateDate], [Action],[FirstName], [LastName], [AuditOpenings], [ExtAccessLevelIDList], [AutoKeyEdit.ROMCode], [ToBeProcessedBySalto])  
	(select CRDHLD.id,     GetDate(),  4, CRDHLD.First_Name, CRDHLD.Last_Name,   1,    case len(isnull(CRDHLD.AG_Name,''))  when 0 then ''
   else left(rtrim(CRDHLD.AG_Name), len(rtrim(CRDHLD.ag_name))-1)  
   END,            DELETED.Code + '000000' ,    1
	from  DELETED inner join CRDHLD on CRDHLD.id = DELETED.Owner
	where
	CRDHLD.id not in (select ExtUserID from CardHolderSyncTable))

	update CardHolderSyncTable set
	[LastUpdateDate] = GetDate(), [Action] = 4,[FirstName] = CRDHLD.First_Name, [LastName] = CRDHLD.Last_Name, [AuditOpenings] = 1, [ExtAccessLevelIDList] = case len(isnull(CRDHLD.AG_Name,''))  when 0 then ''
   else left(rtrim(CRDHLD.AG_Name), len(rtrim(CRDHLD.ag_name))-1)  
   END , [AutoKeyEdit.ROMCode] = DELETED.Code + '000000'  , [ToBeProcessedBySalto] = 1
	from  DELETED inner join CRDHLD on CRDHLD.id = DELETED.Owner
	where 
	CRDHLD.id in (select ExtUserID from CardHolderSyncTable)
	AND CardHolderSyncTable.ExtUserID = CRDHLD.id
	
END


GO

DROP TRIGGER [dbo].[CARDsyncTrigUpdate]
GO

CREATE TRIGGER [dbo].[CARDsyncTrigUpdate] ON [dbo].[Card] 
AFTER UPDATE,INSERT 
AS 
BEGIN 

	SET NOCOUNT ON; 


	insert into CardHolderSyncTable 
	([ExtUserID], [LastUpdateDate], [Action],[FirstName], [LastName], [AuditOpenings], [ExtAccessLevelIDList], [AutoKeyEdit.ROMCode], [ToBeProcessedBySalto])  
	(select CRDHLD.id,     GetDate(),     3,      CRDHLD.First_Name, CRDHLD.Last_Name,     1,  case len(isnull(CRDHLD.AG_Name,''))  when 0 then ''
   else left(rtrim(CRDHLD.AG_Name), len(rtrim(CRDHLD.ag_name))-1)  
   END,      INSERTED.Code + '000000' , 1
	from  INSERTED inner join CRDHLD on CRDHLD.id = INSERTED.Owner
	where 
	INSERTED.Owner not in (select ExtUserID from CardHolderSyncTable))

	update CardHolderSyncTable set
	[LastUpdateDate] = GetDate(), [Action] = 3,[FirstName] = CRDHLD.First_Name, [LastName] = CRDHLD.Last_Name, [AuditOpenings] = 1, [ExtAccessLevelIDList] = case len(isnull(CRDHLD.AG_Name,''))  when 0 then ''
   else left(rtrim(CRDHLD.AG_Name), len(rtrim(CRDHLD.ag_name))-1)  
   END, [AutoKeyEdit.ROMCode] = INSERTED.Code + '000000' , [ToBeProcessedBySalto] = 1
	from  INSERTED inner join CRDHLD on CRDHLD.id = INSERTED.Owner
	where INSERTED.Owner in (select ExtUserID from CardHolderSyncTable) AND INSERTED.Owner > 0
	AND CardHolderSyncTable.ExtUserID = INSERTED.Owner 
	
	update CardHolderSyncTable set
	[LastUpdateDate] = GetDate(), [Action] = 3,[FirstName] = CRDHLD.First_Name, [LastName] = CRDHLD.Last_Name, [AuditOpenings] = 1, [ExtAccessLevelIDList] = case len(isnull(CRDHLD.AG_Name,''))  when 0 then ''
   else left(rtrim(CRDHLD.AG_Name), len(rtrim(CRDHLD.ag_name))-1)  
   END, [AutoKeyEdit.ROMCode] = INSERTED.Code + '000000' , [ToBeProcessedBySalto] = 1
	from  DELETED inner join CRDHLD on CRDHLD.id = DELETED.Owner left join INSERTED on CRDHLD.id = INSERTED.Owner
	where DELETED.Owner in (select ExtUserID from CardHolderSyncTable) AND (INSERTED.Owner = 0 OR INSERTED.Owner is null)
	AND CardHolderSyncTable.ExtUserID = DELETED.Owner 
END

GO